USE [RD_SalesMart]
GO

/****** Object:  View [dbo].[Sales_Star_Customer]    Script Date: 11/17/2012 14:26:43 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

--DROp VIEW [dbo].[Sales_Star_Customer] 
CREATE VIEW [dbo].[Sales_Star_Customer] AS
SELECT     A.CustomerNumberID, A.SourceID, A.Source, ParentNumber, ParentName, ParentAddressLine01, ParentAddressLine02, ParentAddressLine03, 
                      ParentAddressLine04, ParentPostalCode, ParentCity, ParentCounty, ParentStateCode, ParentState, ParentCountry, ParentCountryCode, 
                      InvoicetoNumber, InvoicetoName, InvoicetoAddressLine01, InvoicetoAddressLine02, InvoicetoAddressLine03, InvoicetoAddressLine04, 
                      InvoicetoPostalCode, InvoicetoCity, InvoicetoCounty, InvoicetoStateCode, InvoicetoState, InvoicetoCountry, InvoicetoCountryCode, 
                      InvoicetoSearchTypeCode, InvoicetoSearchType, SoldtoNumber, SoldtoName, SoldtoAddressLine01, SoldtoAddressLine02, SoldtoAddressLine03, 
                      SoldtoAddressLine04, SoldtoPostalCode, SoldtoCity, SoldtoCounty, SoldtoStateCode, SoldtoState, SoldtoCountry, SoldtoCountryCode, 
                      SoldtoSearchTypeCode, SoldtoSearchType, ShiptoNumber, ShiptoName, ShiptoAddressLine01, ShiptoAddressLine02, ShiptoAddressLine03, 
                      ShiptoAddressLine04, ShiptoPostalCode, ShiptoCity, ShiptoCounty, ShiptoStateCode, ShiptoState, ShiptoCountry, ShiptoCountryCode, 
                      ShiptoSearchTypeCode, ShiptoSearchType, RelatedAddressNumber, CompanyNumber, CreditLimit, 
A.Category01Code, A.Category01, A.Category02Code, A.Category02, A.Category03Code, 
A.Category03, A.Category04Code, 
A.Category04, A.Category05Code, A.Category05, 
A.Category06Code, A.Category06, A.Category07Code, A.Category07, A.Category08Code, A.Category08, 
A.Category09Code, A.Category09, 
A.Category10Code, A.Category10, 
A.Category11Code, A.Category11,
A.Category12Code, A.Category12, 
A.Category13Code, A.Category13, 
A.Category14Code, A.Category14, 
A.Category15Code, A.Category15, 
A.Category16Code, A.Category16,
A.Category17Code, A.Category17, 
A.Category18Code, A.Category18, 
A.Category19Code, A.Category19, 
A.Category20Code, A.Category20, 
A.Category21Code, A.Category21, 
A.Category22Code, A.Category22, 
A.Category23Code, A.Category23, 
A.Category24Code, A.Category24, 
A.Category25Code, A.Category25, 
A.Category26Code,  A.Category26, 
A.Category27Code, A.Category27, 
A.Category28Code, A.Category28, 
A.Category29Code, A.Category29, 
A.Category30Code, A.Category30, 
A.InsertIndicator,
A.InsertDate, A.ChangeIndicator, A.ChangeDate, A.DeleteIndicator, A.DeleteDate, A.PurgeDate, 
A.DateOfFirstInvoice, LastInvoiceDate, LastStatementDate,
						RouteCode,
      C.[RelatedAddressNumber1]
      ,C.[RelatedAddressNumber2]
      ,C.[RelatedAddressNumber3]
      ,C.[RelatedAddressNumber4]
      ,C.[RelatedAddressNumber5]
          ,[CreditMessageCode]
      ,[CreditMessage],
						
ROUTECODEDescription,HoldOrdersCode,HOLDORDERSCODEDescription,StopCode,DELIVERYDAY,
						CASE WHEN B.Remark IS NULL
						THEN 'N/A' ELSE B.Remark END AS Remark,
						CASE WHEN B.NameRemark IS NULL
						THEN 'N/A' ELSE B.NameRemark END AS Remark2,
						CASE WHEN B.TextRemark IS NULL
						THEN 'N/A' ELSE B.TextRemark END AS Remark3,
						CASE WHEN B.UDCValues IS NULL
						THEN 'N/A' ELSE B.UDCValues END AS UDCValue
FROM         
	RD_Common.dbo.Sales_Star_Customer A 
left outer join 
	RD_CommonWork.dbo.Join_Customer_Suppl B

on 
	B.CustomerNumber = A.ShiptoNumber and B.Sourceid = A.Sourceid 
join 
	[RD_COMMON].[dbo].[Star_AddressBook] C
on
	A.Sourceid = C.Sourceid and A.ShiptoNumber = C.AddressNumber
GO


